import Router from "koa-router";
import { authMiddleware } from "../../../middleware/auth";
import { pool } from "../../../config/database";
import { RowDataPacket, ResultSetHeader } from "mysql2";
import { 
  checkAdminPermission, 
  getPaginationParams, 
  createPaginatedResponse, 
  getSearchParams,
  handleError,
  successResponse,
  validateId,
  validateBatchIds
} from "../common";

const router = new Router({ prefix: "/api/admin/carts" });

// 获取用户购物车列表 - 支持搜索和分页
router.get("/", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { page, limit, offset } = getPaginationParams(ctx);
    const { keyword, hasItems, startDate, endDate } = getSearchParams(ctx);

    // 构建查询条件
    let whereClause = "WHERE 1=1";
    const params: any[] = [];

    if (keyword) {
      whereClause += " AND (u.username LIKE ? OR u.email LIKE ? OR u.nickname LIKE ?)";
      const searchTerm = `%${keyword}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }

    if (hasItems !== undefined) {
      if (hasItems === 'true') {
        whereClause += " AND cart_stats.item_count > 0";
      } else if (hasItems === 'false') {
        whereClause += " AND (cart_stats.item_count = 0 OR cart_stats.item_count IS NULL)";
      }
    }

    if (startDate) {
      whereClause += " AND cart_stats.last_updated >= ?";
      params.push(startDate);
    }

    if (endDate) {
      whereClause += " AND cart_stats.last_updated <= ?";
      params.push(endDate + ' 23:59:59');
    }

    // 获取总数
    const [countResult] = await pool.execute(`
      SELECT COUNT(*) as total 
      FROM users u 
      LEFT JOIN (
        SELECT 
          user_id,
          COUNT(*) as item_count,
          SUM(quantity) as total_quantity,
          SUM(quantity * unit_price) as total_value,
          MAX(updated_at) as last_updated
        FROM cart_items 
        GROUP BY user_id
      ) cart_stats ON u.id = cart_stats.user_id
      ${whereClause}
    `, params) as [RowDataPacket[], any];
    const total = countResult[0].total;

    // 获取用户购物车列表
    const [carts] = await pool.execute(`
      SELECT 
        u.id as user_id,
        u.username,
        u.email,
        u.nickname,
        u.created_at as user_created_at,
        u.last_login_at,
        COALESCE(cart_stats.item_count, 0) as item_count,
        COALESCE(cart_stats.total_quantity, 0) as total_quantity,
        COALESCE(cart_stats.total_value, 0) as total_value,
        cart_stats.last_updated as cart_last_updated
      FROM users u 
      LEFT JOIN (
        SELECT 
          user_id,
          COUNT(*) as item_count,
          SUM(quantity) as total_quantity,
          SUM(quantity * unit_price) as total_value,
          MAX(updated_at) as last_updated
        FROM cart_items 
        GROUP BY user_id
      ) cart_stats ON u.id = cart_stats.user_id
      ${whereClause}
      ORDER BY cart_stats.last_updated DESC, u.last_login_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `, params) as [RowDataPacket[], any];

    const response = createPaginatedResponse(carts, total, page, limit);
    successResponse(ctx, response, "获取购物车列表成功");

  } catch (error) {
    handleError(ctx, error, "获取购物车列表失败");
  }
});

// 获取用户购物车详情
router.get("/:userId", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const userId = validateId(ctx, 'userId');
    if (userId === null) return;

    // 获取用户基本信息
    const [users] = await pool.execute(
      "SELECT id, username, email, nickname, created_at, last_login_at FROM users WHERE id = ?",
      [userId]
    ) as [RowDataPacket[], any];

    if (users.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "用户不存在" };
      return;
    }

    const user = users[0];

    // 获取购物车商品详情
    const [cartItems] = await pool.execute(`
      SELECT 
        ci.*,
        p.name as product_name,
        p.price as product_current_price,
        p.image_url as product_image,
        p.category as product_category,
        p.stock_quantity as product_stock,
        p.status as product_status,
        c.name as config_name,
        c.total_price as config_current_price,
        c.image_url as config_image,
        c.category as config_category
      FROM cart_items ci
      LEFT JOIN products p ON ci.product_id = p.id
      LEFT JOIN configs c ON ci.config_id = c.id
      WHERE ci.user_id = ?
      ORDER BY ci.created_at DESC
    `, [userId]) as [RowDataPacket[], any];

    // 处理购物车商品数据
    const processedItems = cartItems.map(item => {
      const currentPrice = item.product_id ? item.product_current_price : item.config_current_price;
      const isOutOfStock = item.product_id && item.product_stock <= 0;
      const isInactive = item.product_id && item.product_status !== 'active';
      
      return {
        ...item,
        productInfo: item.product_id ? {
          id: item.product_id,
          name: item.product_name,
          currentPrice: item.product_current_price,
          image_url: item.product_image,
          category: item.product_category,
          stock: item.product_stock,
          status: item.product_status,
          isOutOfStock,
          isInactive
        } : null,
        configInfo: item.config_id ? {
          id: item.config_id,
          name: item.config_name,
          currentPrice: item.config_current_price,
          image_url: item.config_image,
          category: item.config_category
        } : null,
        priceChanged: currentPrice !== item.unit_price,
        subtotal: item.quantity * item.unit_price,
        currentSubtotal: item.quantity * currentPrice
      };
    });

    // 计算购物车统计
    const cartSummary = {
      itemCount: cartItems.length,
      totalQuantity: cartItems.reduce((sum, item) => sum + item.quantity, 0),
      originalTotal: cartItems.reduce((sum, item) => sum + (item.quantity * item.unit_price), 0),
      currentTotal: cartItems.reduce((sum, item) => {
        const currentPrice = item.product_id ? item.product_current_price : item.config_current_price;
        return sum + (item.quantity * currentPrice);
      }, 0),
      outOfStockItems: processedItems.filter(item => item.productInfo?.isOutOfStock).length,
      inactiveItems: processedItems.filter(item => item.productInfo?.isInactive).length,
      priceChangedItems: processedItems.filter(item => item.priceChanged).length
    };

    const cartDetail = {
      user,
      items: processedItems,
      summary: cartSummary
    };

    successResponse(ctx, cartDetail, "获取购物车详情成功");

  } catch (error) {
    handleError(ctx, error, "获取购物车详情失败");
  }
});

// 清理用户购物车
router.delete("/:userId", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const userId = validateId(ctx, 'userId');
    if (userId === null) return;

    // 检查用户是否存在
    const [users] = await pool.execute(
      "SELECT id FROM users WHERE id = ?",
      [userId]
    ) as [RowDataPacket[], any];

    if (users.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "用户不存在" };
      return;
    }

    // 删除用户购物车商品
    const [result] = await pool.execute(
      "DELETE FROM cart_items WHERE user_id = ?",
      [userId]
    ) as [ResultSetHeader, any];

    successResponse(ctx, { 
      userId, 
      deletedItems: result.affectedRows 
    }, `清理购物车成功，删除${result.affectedRows}个商品`);

  } catch (error) {
    handleError(ctx, error, "清理购物车失败");
  }
});

// 清理指定购物车商品
router.delete("/:userId/items/:itemId", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const userId = validateId(ctx, 'userId');
    const itemId = validateId(ctx, 'itemId');
    if (userId === null || itemId === null) return;

    // 检查购物车商品是否存在
    const [items] = await pool.execute(
      "SELECT id FROM cart_items WHERE id = ? AND user_id = ?",
      [itemId, userId]
    ) as [RowDataPacket[], any];

    if (items.length === 0) {
      ctx.status = 404;
      ctx.body = { success: false, message: "购物车商品不存在" };
      return;
    }

    // 删除购物车商品
    await pool.execute(
      "DELETE FROM cart_items WHERE id = ? AND user_id = ?",
      [itemId, userId]
    );

    successResponse(ctx, { userId, itemId }, "删除购物车商品成功");

  } catch (error) {
    handleError(ctx, error, "删除购物车商品失败");
  }
});

// 批量清理空购物车
router.post("/batch-clean-empty", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    // 查找空购物车用户（没有购物车商品的用户）
    const [emptyCartUsers] = await pool.execute(`
      SELECT u.id, u.username 
      FROM users u 
      LEFT JOIN cart_items ci ON u.id = ci.user_id 
      WHERE ci.user_id IS NULL
    `) as [RowDataPacket[], any];

    // 由于这些用户本身就没有购物车商品，所以这个操作主要是统计
    successResponse(ctx, { 
      emptyCartUsers: emptyCartUsers.length,
      users: emptyCartUsers 
    }, `发现${emptyCartUsers.length}个空购物车用户`);

  } catch (error) {
    handleError(ctx, error, "批量清理空购物车失败");
  }
});

// 批量清理过期购物车商品
router.post("/batch-clean-expired", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    const { days = 30 } = ctx.request.body as { days?: number };

    if (days <= 0 || days > 365) {
      ctx.status = 400;
      ctx.body = { success: false, message: "天数必须在1-365之间" };
      return;
    }

    // 删除指定天数前的购物车商品
    const [result] = await pool.execute(
      "DELETE FROM cart_items WHERE updated_at < DATE_SUB(NOW(), INTERVAL ? DAY)",
      [days]
    ) as [ResultSetHeader, any];

    successResponse(ctx, { 
      cleanedDays: days,
      deletedItems: result.affectedRows 
    }, `清理${days}天前的购物车商品成功，删除${result.affectedRows}个商品`);

  } catch (error) {
    handleError(ctx, error, "批量清理过期购物车商品失败");
  }
});

// 获取购物车统计信息
router.get("/statistics/overview", authMiddleware, async (ctx) => {
  if (!checkAdminPermission(ctx)) return;
  
  try {
    // 购物车总体统计
    const [cartStats] = await pool.execute(`
      SELECT 
        COUNT(DISTINCT user_id) as users_with_cart,
        COUNT(*) as total_cart_items,
        SUM(quantity) as total_quantity,
        SUM(quantity * unit_price) as total_cart_value,
        AVG(quantity * unit_price) as avg_item_value
      FROM cart_items
    `) as [RowDataPacket[], any];

    // 用户统计
    const [userStats] = await pool.execute(`
      SELECT 
        COUNT(*) as total_users,
        COUNT(*) - COALESCE(cart_users.users_with_cart, 0) as users_without_cart
      FROM users u
      CROSS JOIN (
        SELECT COUNT(DISTINCT user_id) as users_with_cart FROM cart_items
      ) cart_users
    `) as [RowDataPacket[], any];

    // 商品类型统计
    const [typeStats] = await pool.execute(`
      SELECT 
        COUNT(CASE WHEN product_id IS NOT NULL THEN 1 END) as product_items,
        COUNT(CASE WHEN config_id IS NOT NULL THEN 1 END) as config_items
      FROM cart_items
    `) as [RowDataPacket[], any];

    // 活跃度统计（最近不同时间段的购物车活动）
    const [activityStats] = await pool.execute(`
      SELECT 
        COUNT(CASE WHEN updated_at >= DATE_SUB(NOW(), INTERVAL 1 DAY) THEN 1 END) as activity_1day,
        COUNT(CASE WHEN updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as activity_7days,
        COUNT(CASE WHEN updated_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as activity_30days
      FROM cart_items
    `) as [RowDataPacket[], any];

    // 热门商品统计
    const [topProducts] = await pool.execute(`
      SELECT 
        p.name as product_name,
        COUNT(*) as cart_count,
        SUM(ci.quantity) as total_quantity
      FROM cart_items ci
      JOIN products p ON ci.product_id = p.id
      GROUP BY ci.product_id, p.name
      ORDER BY cart_count DESC
      LIMIT 10
    `) as [RowDataPacket[], any];

    successResponse(ctx, {
      overview: {
        ...cartStats[0],
        ...userStats[0],
        ...typeStats[0]
      },
      activity: activityStats[0],
      topProducts
    }, "获取购物车统计成功");

  } catch (error) {
    handleError(ctx, error, "获取购物车统计失败");
  }
});

export default router; 